/*
 * Copyright (c) 2011 CCXE(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCXE(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCXE(China) Co.,Ltd.
 *
 * Author: zhaoshujin
 * Date: 2012-3-1 上午11:48:11
 */
package com.jxl.excelplugin;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;

/**
 * 比对财报指标编码，保证相同指标对应的字段相同
 * @author zhaoshujin
 * 
 */
public class ContrastIndicator {

	public static String sourceFile = "F:\\MYDOCUMENT\\ExcelPlugin\\文档\\需求\\后期修改\\Excel插件财务报表字段比对_20120301.xls";

	public void contrast() {

		try {

			Workbook wb = Workbook.getWorkbook(new File(sourceFile));

			Sheet newFinancialInsurance = wb.getSheet("新准则-保险公司");
			Sheet newFinancialSecurities = wb.getSheet("新准则-证券公司");
			Sheet newFinancialBusiness = wb.getSheet("新准则-商业银行");

			// 新准则-利润表
			contrastCheck(newFinancialInsurance, newFinancialSecurities, 3, 62, 3, 48);
			contrastCheck(newFinancialInsurance, newFinancialBusiness, 3, 62, 3, 48);
			contrastCheck(newFinancialSecurities, newFinancialBusiness, 3, 48, 3, 48);

			// 新准则-资产负债表
			contrastCheck(newFinancialInsurance, newFinancialSecurities, 68, 165, 54, 131);
			contrastCheck(newFinancialInsurance, newFinancialBusiness, 68, 165, 54, 127);
			contrastCheck(newFinancialSecurities, newFinancialBusiness, 54, 131, 54, 127);
			
			// 新准则-现金流量表
			contrastCheck(newFinancialInsurance, newFinancialSecurities, 171, 272, 137, 236);
			contrastCheck(newFinancialInsurance, newFinancialBusiness, 171, 272, 133, 234);
			contrastCheck(newFinancialSecurities, newFinancialBusiness, 137, 236, 133, 234);

			wb.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	public void contrastCheck(Sheet sheet1, Sheet sheet2, int s1s, int s1e, int s2s, int s2e) {

		int rows1 = s1e - s1s + 1;
		int rows2 = s2e - s2s + 1;

		String item1 = null;
		String field1 = null;
		String oldfield1 = null;
		String item2 = null;
		String field2 = null;
		String oldfield2 = null;

		String[][] sheet1Array = new String[rows1][3];
		String[][] sheet2Array = new String[rows2][3];

		for (int r1 = s1s - 1, rr = 0; r1 < s1e; r1++) {
			item1 = sheet1.getCell(1, r1).getContents().toLowerCase().trim();
			field1 = sheet1.getCell(2, r1).getContents().toLowerCase().trim();
			oldfield1 = sheet1.getCell(3, r1).getContents().toLowerCase().trim();

			if (item1 != null && !"".equals(item1)) {
				sheet1Array[rr][0] = item1;
				sheet1Array[rr][1] = field1;
				sheet1Array[rr][2] = oldfield1;
				rr++;
			}
		}

		for (int r2 = s2s - 1, rr = 0; r2 < s2e; r2++) {
			item2 = sheet2.getCell(1, r2).getContents().toLowerCase().trim();
			field2 = sheet2.getCell(2, r2).getContents().toLowerCase().trim();
			oldfield2 = sheet2.getCell(3, r2).getContents().toLowerCase().trim();

			if (item2 != null && !"".equals(item2)) {
				sheet2Array[rr][0] = item2;
				sheet2Array[rr][1] = field2;
				sheet2Array[rr][2] = oldfield2;
				rr++;
			}
		}

		// contrast
		for (int r1 = 0; r1 < sheet1Array.length; r1++) {
			item1 = sheet1Array[r1][0];
			field1 = sheet1Array[r1][1];
			oldfield1 = sheet1Array[r1][2];

			for (int r2 = 0; r2 < sheet2Array.length; r2++) {
				item2 = sheet2Array[r2][0];
				field2 = sheet2Array[r2][1];
				oldfield2 = sheet2Array[r2][2];

				if (item1 != null && item1.equals(item2) && (!field1.equals(field2)) ) {
					System.out.println("============================= new");
					System.out.println(sheet1.getName() + ":" + item1 + ":" + field1);
					System.out.println(sheet2.getName() + ":" + item2 + ":" + field2);
				}
				
				if (item1 != null && item1.equals(item2) && (!oldfield1.equals(oldfield2)) ) {
					System.out.println("============================= old");
					System.out.println(sheet1.getName() + ":" + item1 + ":" + oldfield1);
					System.out.println(sheet2.getName() + ":" + item2 + ":" + oldfield2);
				}
			}
		}
	}

	public static void main(String[] args) {

		ContrastIndicator ci = new ContrastIndicator();

		long start = System.currentTimeMillis();

		ci.contrast();

		long end = System.currentTimeMillis();
		System.out.println(end - start);
	}

}
